GeoPandas is a Python library that simplifies working with geospatial data. It extends Pandas' capabilities to handle geographic information, allowing easy data manipulation, spatial operations, and visualization. By integrating geometry, attributes, and mapping functionalities, Geopandas facilitates tasks like spatial analysis, querying, and creating maps.

Folium is another Python library that builds on the data wrangling strengths of the Python ecosystem and the mapping strengths of the Leaflet.js library. It allows you to manipulate your data in Python, then visualize it in a Leaflet map via Folium.

Get the code from my GitHub repository

Importing required libraries:

%matplotlib inline
import pandas as pd
import geopandas as gpd
import psycopg2
import psycopg2.extras
import folium
import json

Establishing connection with PostgreSQL(postgis) server. Keeping my credentials secret for this demonstration. I prefer to use data from a PostGIS enabled server. You can always modify the data on the fly through SQL.

df = pd.read_csv(r"D:\Dropbox\Touhid Personal\credentials\postgresql_credentials.csv")

conn = psycopg2.connect (
    host = df.loc[0,'host'],
    port = df.loc[0,'port'],
    dbname = df.loc[0,'database'],
    user = df.loc[0,'username'],
    password = df.loc[0,'password']
)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

Initializing Folium mapping canvas and setting a starting point. Adding some basemaps.

mp = folium.Map(location=[23.810331,90.412521], zoom_start=13,
                control_scale=True, tiles='CartoDB Positron', name = 'CartoDB Positron')
folium.TileLayer(tiles='http://services.arcgisonline.com/ArcGIS/rest/services/World_Imagery/MapServer/tile/{z}/{y}/{x}', name='Esri Imagery', attr='ESRI World Imagery').add_to(mp)
folium.TileLayer('Stamen Watercolor', name='Stamen Watercolor').add_to(mp)
folium.TileLayer('OpenStreetMap', name='OpenStreetMap').add_to(mp)

<folium.raster_layers.TileLayer at 0x26ed12b1350>

Taking input from user.

dma_id = input()

510

Passing the user input to PostgreSQL database and retrieving the study area boundary. GeoPandas has been used to handle the data. Though, the official documentation of GeoPandas suggests to use SQLAlchemy to connect with databases, I really like the Psycopg2 for this purpose. It does not same work. Fetching data and styling it at the same time.

def style_dma(ftr):
    return {'color':'black','fillOpacity':0, 'weight':4}

dma = gpd.GeoDataFrame.from_postgis(f"SELECT *from all_dma where dma_id = '{dma_id}' ",conn)
dma.to_crs(epsg=4326, inplace=True)
#converting the object to json
dma_json = dma.to_json()
#converting the json to geojson
dma_geojson = folium.features.GeoJson(dma_json, name='DMA Boundary', style_function=style_dma).add_to(mp)

Though an initial point has been set up at the time of initializing Folium, it is highly unlikely that the study area will be at that location. Therefore, getting the minimum and maximum values for both latitude and longitude of our area of interest, and setting the map canvas accordingly.

minx = dma.bounds.loc[0,'minx']
miny = dma.bounds.loc[0,'miny']
maxx = dma.bounds.loc[0,'maxx']
maxy = dma.bounds.loc[0,'maxy']
mp.fit_bounds([[miny,minx],[maxy,maxx]])
mp

Make this Notebook Trusted to load map: File -> Trust Notebook

The complete map will contain a number of features, which are: valves, pipelines, deep tube wells, DMA Chambers, service connection lines, buildings and consumer meter. Among these valves, deep tube wells, DMA Chambers, and consumer meter are of point type. Pipelines are multipolyline, and buildings are multipolygon. I will load these features one by one from the PostgreSQL server and style them accordingly.

Loading pipelines and setting proper styling

The basic process is that Folium takes a GeoJSON object, iterates through each feature, apply prefixed styling, and add the features on map

#PIPELINES

def style_pipeline(ftr):
    color_dict = {
        '50': '#99bbff',
        '75': '#1a66ff',
        '100': '#008080',
        '110': '#66ff99',
        '150': '#00e64d',
        '160': '#006622',
        '200': '#ffb380',
        '250': '#ff6600',
        '300': '#b34700',
        '315': '#d9b3ff',
        '355': '#a64dff',
        '400': '#4d0099',
        '450': '#8585ad',
        '560': '#cc6699',
    }
    
    pipeline_diameter = ftr['properties']['pipeline_diameter']
    color = color_dict.get(pipeline_diameter, 'black')
    return {'color': color}

pipelines = gpd.GeoDataFrame.from_postgis(f"SELECT pipeline_database_id, pipeline_dma_id, pipeline_address, pipeline_diameter, pipeline_material, pipeline_category, pipeline_length, geom from pipelines where pipeline_dma_id = '{dma_id}'",conn)
pipelines.to_crs(epsg=4326, inplace=True)
#converting the object to json
pipelines_json = pipelines.to_json()
#converting the json to geojson
pipelines_geojson = folium.features.GeoJson(pipelines_json,
                                            name='Pipelines',
                                            tooltip= folium.features.GeoJsonTooltip(
                                                ['pipeline_database_id','pipeline_diameter','pipeline_material','pipeline_category','pipeline_length'],
                                                ['Pipeline ID:','Diameter (mm):','Meterial:','Category:','Length (m):']),
                                            style_function=style_pipeline
                                           ).add_to(mp)



mp

Make this Notebook Trusted to load map: File -> Trust Notebook

Loading Deep Tube Well and setting style

For simplicity, no category has been applied on deep tubewell. So, a simple marker will do. Folium can take any Glyphicon of Bootstrap, which is really nice.

#PTW

ptw = gpd.GeoDataFrame.from_postgis(f"SELECT ptw_id, ptw_dma_id, ptw_location, geom from ptw where ptw_dma_id = '{dma_id}'",conn)
ptw.to_crs(epsg=4326, inplace=True)

ptw_json = ptw.to_json()

ptw_geojson = folium.features.GeoJson(ptw_json, name='Deep Tube Well',
                                      tooltip= folium.features.GeoJsonTooltip(
                                      ['ptw_id', 'ptw_location'],
                                      ['Tubewell ID:','Location']
                                      ),
                                      marker = folium.Marker(icon=folium.Icon(color="blue", icon="tint"))
                                     ).add_to(mp)
mp

Make this Notebook Trusted to load map: File -> Trust Notebook

Loading DMA Chamber and setting style

#DMA Chamber

ice = gpd.GeoDataFrame.from_postgis(f"SELECT ice_id, ice_dma_id, ice_type, ice_location, geom from ice where ice_dma_id = '{dma_id}'",conn)
ice.to_crs(epsg=4326, inplace=True)

ice_json = ice.to_json()

ice_geojson = folium.features.GeoJson(ice_json, name='DMA Chamber',
                                     popup=folium.features.GeoJsonPopup(
                                     ['ice_id', 'ice_type', 'ice_location'],
                                     ['Chamber ID:','Chamber Type:','Chamber Location']),
                                       marker = folium.Marker(icon=folium.Icon(color="red", icon="stop"))
                                     ).add_to(mp)
mp

Make this Notebook Trusted to load map: File -> Trust Notebook

Loading Service Connection and setting style

Service connection is a polyline feature. For styling a color shade from orage to crimson has been applied according the montly billed amount.

#SERVICE CONNECTIONS 

def style_hcl(ftr):
    billed_amt = ftr['properties']['billed_amt']
    
    if billed_amt>0 and billed_amt<=2000:
        shade = 'Orange'
    elif billed_amt>2000 and billed_amt<=5000:
        shade = 'LightSalmon'
    elif billed_amt>5000 and billed_amt<=10000:
        shade = 'Coral'
    elif billed_amt>10000 and billed_amt<=15000:
        shade = 'OrangeRed'
    elif billed_amt>15000:
        shade = 'Crimson'
    else:
        shade = 'black'
    
    return {'color':shade}

hcl = gpd.GeoDataFrame.from_postgis(f"SELECT hcl_account_no, hcl_meter_no, billed_amt, hcl_dma_id, hcl_category, geom from hcl where hcl_dma_id = '{dma_id}' and not billed_amt is null ",conn)
hcl.to_crs(epsg=4326, inplace=True)

hcl_json = hcl.to_json()

hcl_geojson = folium.features.GeoJson(hcl_json, name='Service Connection',
                                     tooltip=folium.features.GeoJsonTooltip(
                                     ['hcl_account_no','hcl_meter_no','billed_amt','hcl_category'],
                                     ['Account Number:','Meter No:','Billed Amount:','Category:']),
                                      style_function=style_hcl
                                     ).add_to(mp)
mp

Make this Notebook Trusted to load map: File -> Trust Notebook

Loading Buildings and setting style

Building layer is a bit messy, so I prefer to set the 'show' value to 'False'. It will be on the map, but not on the page load. It can be displayed by applying the tick mark in the layer control.

#BUILDINGS
def style_building(ftr):
    return {'color':'#95A5A6', 'fillOpacity':0}

buildings = gpd.GeoDataFrame.from_postgis(f"SELECT building_dma_id, building_category, building_storey, building_population, building_address, geom from buildings where building_dma_id = '{dma_id}'",conn)
buildings.to_crs(epsg=4326, inplace=True)

buildings_json = buildings.to_json()

buildings_geojson = folium.features.GeoJson(buildings_json, name='Buildings', show=False,
                                           tooltip=folium.features.GeoJsonTooltip(
                                           ['building_category', 'building_storey', 'building_population', 'building_address'],
                                           ['Category:','Storey:','Population:','Address:']
                                           ),
                                            style_function=style_building
                                           ).add_to(mp)

Loading Consumer Meters and applying style

Consumer meter is a point feature. Applying a single style to a point feature in Folium is pretty simple as displayed in the case of deep tubewell. However, if you want to apply multiple style on point feature, it gets a bit complicated. You have to iterate through each point feature, and catch the properties upon which you want to apply the symbology. In our case, the radius of the point marker will vary according to the diameter of the meters. Again the color will vary according to the category of the meters (domestic, commercial or others).

mtr = gpd.GeoDataFrame.from_postgis(f"SELECT mtr_no, cast(mtr_diameter as integer)as diameter, mtr_cus_type, geom from mtr where mtr_dma_id = '{dma_id}' and not mtr_cus_type is null and not mtr_diameter is null ",conn)
mtr.to_crs(epsg=4326, inplace=True)

for index, row in mtr.iterrows():
     
    meter_type = row['mtr_cus_type']
    meter_diameter = row['diameter']
        
    geom = row['geom']
    latitude = geom.y
    longitude = geom.x
    
    if meter_type == 'Domestic':
        color = '#2E86C1'
    elif meter_type == 'Commercial':
        color = '#9B59B6'
    else:
        color = '#E59866'
    
    if meter_diameter <=25:
        radius = 3
    elif meter_diameter>25 and meter_diameter<=50:
        radius = 4.5
    elif meter_diameter>50 and meter_diameter<=100:
        radius = 5
    elif meter_diameter>100 and meter_diameter <=200:
        radius = 6
    elif meter_diameter>200:
        radius = 7
    else:
        radius = 1
        
    tooltip_content = f"<h6>Meter</h6>Meter Type: {meter_type}<br>Meter Diameter (mm): {meter_diameter}"
    
    folium.CircleMarker(
        location = [latitude, longitude],
        radius = radius,
        color = color,
        fill = True,
        fillOpacity=1,
        tooltip = tooltip_content,
        Name="Meter"
    ).add_to(mp)

mp

Make this Notebook Trusted to load map: File -> Trust Notebook

Final map with control layers

From the Control Layer, you can turn off and on any layer. You can also change the basemaps.

folium.LayerControl().add_to(mp)
mp

Make this Notebook Trusted to load map: File -> Trust Notebook

Prepared By: Md. Touhidur Rahman, Email: touhidur002@gmail.com